package core.util.database.index.helper;

import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;

import backend.attribute.manager.AttributeSetManager;

import core.attribute.model.AttributeSet;
import core.product.model.Product;
import core.util.HibernateCoreUtil;
public class AttributeSetFilterIndex {

	/**
	 * @param args
	 */
	public static Set<Long> getAssignedAttribute(Product p,Long attributeSetUuid,Session session)
	{
		/*
		 * This is for checkbox type attribute values
			"select  distinct(attribute_uuid) from productav pav , productav_attributevalue pavav
			where pav.uuid = pavav.productav_uuid and product_uuid = p.getUuid()
				and attributeSet_uuid = attributeSetUuid"
				This query will get the dropdown type attribute value
				
				"select distinct(attribute_uuid) from attribute a, attribute_attributevalue  aav , attributevalue av , productattributedropdownvalue pavdv  where a.uuid = aav.attribute_uuid 
and aav.attributevalue_uuid = av.uuid and av.attributetype = 'dropdown' and pavdv.attributeValue_uuid = aav.attributevalue_uuid
and pavdv.product_uuid = p.getUuid() and a.uuid in (select attribute_uuid from productav where attributeSet_uuid = attributeSetUuid)"
		 */
		Set<Long> attributeUuid = new HashSet<Long>();
		String qryChkbx = "select  distinct(attribute_uuid) from productav pav , productav_attributevalue pavav where pav.uuid = pavav.productav_uuid and product_uuid = " + p.getUuid()+" and attributeSet_uuid = "+ attributeSetUuid ;
		String qryDrpDwn = "select distinct(attribute_uuid) from attribute a, attribute_attributevalue  aav , attributevalue av , productattributedropdownvalue pavdv  where a.uuid = aav.attribute_uuid and aav.attributevalue_uuid = av.uuid and av.attributetype = 'dropdown' and pavdv.attributeValue_uuid = aav.attributevalue_uuid and pavdv.product_uuid = " + p.getUuid()+" and a.uuid in (select attribute_uuid from productav where attributeSet_uuid = "+attributeSetUuid +")";
		Query query = session.createSQLQuery(qryChkbx);
		attributeUuid.addAll(query.list());
		query = session.createSQLQuery(qryDrpDwn);
		attributeUuid.addAll(query.list());
		return attributeUuid;
	}
	public static void runProductFilterIndex(String attributeSet)
	{
		AttributeSetManager asm = new AttributeSetManager();
		AttributeSet as = asm.getAttributeSetByName(attributeSet);
		Session session = HibernateCoreUtil.getSessionFactory().openSession();
		Transaction transaction = null;
		String queryDelete = "drop table if exists " + attributeSet + "_filter_index";
		String createTable = "create table " + attributeSet + "_filter_index(productid bigint(20)";
		String tableName = attributeSet + "_filter_index";
		try 
		{
				transaction = session.beginTransaction();
				Query query = session.createSQLQuery(queryDelete);
				int a = query.executeUpdate();
				System.out.println(a + " rows effected");
				query = session.createSQLQuery("select attribute_uuid from attributeset aset , attributeset_attribute  asa where aset.uuid = asa.attributeset_uuid and aset.name = '"+attributeSet+"'").addScalar("attribute_uuid", Hibernate.LONG);
				List<Long> attrsLst = query.list();
				Iterator<Long> attrsItr = attrsLst.iterator();
				while(attrsItr.hasNext())
				{
					String cols = "c" + attrsItr.next();
					createTable = createTable + " ," + cols + " bit(1)" ;
				}
				createTable = createTable + ")";
				query = session.createSQLQuery(createTable);
				a = query.executeUpdate();
				System.out.println(a + " rows effected");
				// index table is created now getting product one by one....
				Iterator <Product> prodItr = session.createQuery("from Product").list().iterator();
				while(prodItr.hasNext())
				{
					Product p = prodItr.next();
					// check if product exists in product_type_index table
					SQLQuery prodCountquery = session.createSQLQuery("select count(*) count from " + tableName + " where productid =" + p.getUuid()).addScalar("count", Hibernate.INTEGER);
					int cnt = (Integer) prodCountquery.list().get(0);
					if(cnt == 0)
					{
						//insert productId
						String insertQry ="insert into "+tableName+"(productid) values (" + p.getUuid() + ")" ;
						query = session.createSQLQuery(insertQry);
						query.executeUpdate();
					}
					Set<Long> assignedAttributes = (Set<Long>) getAssignedAttribute(p,as.getUuid(),session);
					Iterator<Long>assignedAttributesItr = assignedAttributes.iterator();
					while(assignedAttributesItr.hasNext())
					{
						String col = "c" + assignedAttributesItr.next();
						String updateQry = "update " + tableName + " set " + col + " = 1 where productid = " + p.getUuid();
						query = session.createSQLQuery(updateQry);
						query.executeUpdate();
					}
				}
				transaction.commit();
		}
		catch(HibernateException e)
		{
			// TODO: handle exception
			transaction.rollback();
			e.printStackTrace();
		}
		finally 
		{
			session.close();
		}
	}
	public static void main(String[] args) 
	{
		// TODO Auto-generated method stub
		runProductFilterIndex("Amenities");
		runProductFilterIndex("Attractions");
	}

}
